Amazon Redshift: カーソルの定義内容を確認＆SQLクエリログ詳細情報を取得する

Amazon Redshiftで良く使いそうな便利系SQL

#AWS

#Amazon Redshift

#ビッグデータ

しんや

2016.10.17

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

小ネタです。

現状、Tableau等のBIツールではRedshiftにアクセスする際、カーソルにその定義を含めてRedshiftに対して実行が為される為、Redshiftのクエリ情報を確認してみても以下の様にカーソルの名称のみが表示されるだけで『実際にどんなクエリがTableauからRedshiftに対して発行されていたのか』の詳細が確認出来ません。

redshift-tableau-cursor-configuration-check_01

そこで、当エントリではそのカーソルの内容がどのようなSQLクエリを発行しているのかを確認するSQLを実行する術について紹介して行きたいと思います。

Tableauで生成されたカーソルの中身(の定義文SQL)を確認するSQL

という訳でSQL文がこちら。21行目の『カーソル名称』の部分に実際に発行されたカーソルの名称(文字列情報)を埋めて実行してください。

SELECT
  TRIM(pg_user.usename) AS user_name,
  svl_statementtext.xid,
  svl_statementtext.pid,
  svl_statementtext.starttime,
  svl_statementtext.endtime,
  svl_statementtext.sequence AS seq,
  svl_statementtext.text

FROM
  svl_statementtext
    INNER JOIN 
      (SELECT
        svl_statementtext.xid,
        svl_statementtext.pid,
        svl_statementtext.starttime        
      FROM
        svl_statementtext
          INNER JOIN pg_user ON svl_statementtext.userid = pg_user.usesysid
      WHERE
        text like'declare%<カーソル名称>%'
      ORDER BY
        starttime, xid, pid, sequence
      LIMIT 1) sql_cur_id_info
      ON (
            svl_statementtext.xid = sql_cur_id_info.xid
        AND svl_statementtext.pid = sql_cur_id_info.pid
        AND svl_statementtext.starttime = sql_cur_id_info.starttime)
    INNER JOIN pg_user ON svl_statementtext.userid = pg_user.usesysid
      
ORDER BY
  svl_statementtext.xid,
  svl_statementtext.pid,
  svl_statementtext.starttime,
  svl_statementtext.sequence;

ちなみにこちらのSQL、以下のエントリを元に組み立てたものなのですが、

TableauでRedshiftに実行されたクエリを確認する - Qiita

環境に拠っては実行すると以下の様な実行時エラーを吐く場合があります。

  -----------------------------------------------
  error:  Result size exceeds LISTAGG limit
  code:      8001
  context:   LISTAGG limit: 65535
  query:     33505
  location:  19.cpp:579
  process:   query4_551 [pid=6260]
  -----------------------------------------------

これはSQL文内で用いているLISTAGG関数の文字数制約に拠るエラーに引っ掛かっているものです。SQL文の文字列長が65535バイトを超えたというものですが、項目数の多いテーブルに対して副問合せを繰り返すようなものだったり、BIツールが自動生成するようなクエリの場合はもしかしたらこの条件に合致してしまう、という状況も往々にしてあるかなと思います。(※実際このような形で目にした訳ですし)

LISTAGG 関数 - Amazon Redshift

VARCHAR(MAX)。結果セットが最大 VARCHAR サイズ（64K - 1、または 65535）より大きい場合、LISTAGG は以下のエラーを返します。

LISTAGG関数の扱える文字数上限『65535バイト』という制約についてもシステムに負う所もあるのでこの上限解除も難しいかな、とも思いますし、そもそもそんなに長いクエリを結合してまで見たくもないかなw というのもありますので、『どんなクエリが吐かれているのか分かれば良い』『実行時間や誰が発行したかが分かれば良い』と割り切る形としました。

結果は以下のような形で表示されます。declare文の中身がseqの順番で表示されているので、どのようなSQLが実際生成されていたのかを伺い知る事が可能となります。

結合した状態で見たい場合は『text』列のみ抽出して結果を別途テキストエディタ等に貼り付けて整形すれば良さ気かなと思います。(※ちなみにtext項目は1行200桁となります)

 user_name |   xid   |  pid  |         starttime          |          endtime           | seq |                                                                                                   text                                                                                                   
-----------+---------+-------+----------------------------+----------------------------+-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 zzzz      | 7434230 | 19161 | 2016-10-14 xx:xx:xx.xxxxxx | 2016-10-14 yy:yy:yy.yyyyyy |   0 | declare "SQL_CUR0xa1b2c3d4e5f6g7" cursor with hold for SELECT ...
 zzzz      | 7434230 | 19161 | 2016-10-14 xx:xx:xx.xxxxxx | 2016-10-14 yy:yy:yy.yyyyyy |   1 | AS ... 
 zzzz      | 7434230 | 19161 | 2016-10-14 xx:xx:xx.xxxxxx | 2016-10-14 yy:yy:yy.yyyyyy |   2 | ber of records_ok", ....
 zzzz      | 7434230 | 19161 | 2016-10-14 xx:xx:xx.xxxxxx | 2016-10-14 yy:yy:yy.yyyyyy |   3 | logposts" INNER JOIN .... GROUP BY 1, 2, 3, 4, 6, 7;                                                                       
(4 rows)

任意のキーワードを含むSQLを抽出するSQL

上記SQLを若干カスタマイズして『任意のキーワード』を含むSQLを抽出するSQLが以下となります。表示される項目の列数は上記と同じです。21行目の『キーワード』の部分に検索したい文字列を埋め込んで実行、該当するSQL文が時系列、シークエンス順に表示されます。こちらもSQLが複数行に渡って表示される形となりますので、取得したSQLの加工・連結等されたい場合は上記SQLと同じ手法でお願いします。

SELECT
  TRIM(pg_user.usename) AS user_name,
  svl_statementtext.xid,
  svl_statementtext.pid,
  svl_statementtext.starttime,
  svl_statementtext.endtime,
  svl_statementtext.sequence AS seq,
  svl_statementtext.text

FROM
  svl_statementtext
    INNER JOIN 
      (SELECT DISTINCT
        svl_statementtext.xid,
        svl_statementtext.pid,
        svl_statementtext.starttime        
      FROM
        svl_statementtext
          INNER JOIN pg_user ON svl_statementtext.userid = pg_user.usesysid
      WHERE
        text like'%<キーワード>%'
      ORDER BY
        starttime, xid, pid, sequence) sql_cur_id_info
      ON (
            svl_statementtext.xid = sql_cur_id_info.xid
        AND svl_statementtext.pid = sql_cur_id_info.pid
        AND svl_statementtext.starttime = sql_cur_id_info.starttime)
    INNER JOIN pg_user ON svl_statementtext.userid = pg_user.usesysid
      
ORDER BY
  svl_statementtext.xid,
  svl_statementtext.pid,
  svl_statementtext.starttime,
  svl_statementtext.sequence;

まとめ

当エントリでご紹介した内容はシステムビュー:SVL_STATEMENTTEXTの内容を元に作成したものです。

SVL_STATEMENTTEXT - Amazon Redshift

少し前に投稿した下記エントリでもこのテーブルの内容に触れていますが、このテーブルの内容については保有期限が限られている為、いざ内容を見てみたいとなった時にデータが無い！という場合もありえるかと思います。日次バッチ処理等で別途データを退避させつつ、何らかの経緯でSQLログを確認する必要が出て来た場合等は当エントリの内容でその内容を確認する、と言った運用もアリかもしれませんね。という訳で、こちらからは以上です。